﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Project.Module.DAO.IBL;
using Project.Module.Model;
using Project.Engine.Utilities;

namespace Project.Module.DAO.BL
{
    public class CustomerBL : IBLTable<Customer, int>
    {
        public void InsertCustomer(string name, string email, string address, string mobile, string pass)
        {
            int id = GetMaxId() + 1;
            DateTime date = DateTime.Now;
            pass = SecurityUtil.MD5Encrypt(pass);

            string sql = @"insert into Customer (
                              CustomerID, Username, Password, Firstname, Lastname, 
                              Address, Birthdate, Email, Phonenumber, Sex, 
                              Image, JoinedDate, LastTimeLogIn, CreatedBy, CreatedDate,
                              ModifedDate, ModifiedBy
                           ) values (
                              {0}, {1}, {2}, null, null, 
                              {3}, null, {4}, {5}, CAST('1' AS bit), 
                              null, {6}, {7}, null, null,  
                              null, null
                           )";
            var query = this.Context.ExecuteQuery<CustomerModel>(sql, new object[] { id, name, pass, address, email, mobile, date, date }).ToList();
        }

        public int GetMaxId()
        {
            int Id = 0;
            var query = from e in this.Context.Customers
                        orderby e.CustomerID descending
                        select new { Id = e.CustomerID };

            if (query != null)
            {
                Id = ConvertUtil.ToInt32(query.Select(c => c.Id).FirstOrDefault().ToString());
            }

            // return
            return Id;
        }

        public bool LoginCustomer(string name, string pass)
        {
            pass = SecurityUtil.MD5Encrypt(pass);
            var query = from e in this.Context.Customers
                        where e.Username == name && e.Password == pass
                        select new
                        {
                            Id = e.CustomerID.ToString(),
                            Username = e.Username,
                        };

            if (query != null && query.ToList().Count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public bool CheckAccountCustomer(string name)
        {
            var query = from e in this.Context.Customers
                        where e.Username == name
                        select e.Username;

            if (query != null && query.ToList().Count >= 1)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public int GetIdByUsername(string name)
        {
            int id = 0;
            var query = from e in this.Context.Customers
                        where e.Username == name
                        select new { Id = e.CustomerID.ToString() };
            if (query != null && query.ToList().Count > 0)
            {
                id = ConvertUtil.ToInt32(query.Select(c => c.Id).FirstOrDefault().ToString());
            }

            return id;
        }

        public string GetEmailByUsername(string name)
        {
            string email = String.Empty;
            var query = from e in this.Context.Customers
                        where e.Username == name
                        select new { Email = e.Email.ToString() };
            if (query != null && query.ToList().Count > 0)
            {
                email = ConvertUtil.ToString(query.Select(c => c.Email).FirstOrDefault().ToString());
            }

            return email;
        }

        public CustomerModel GetUserInformation(string name)
        {
            //CustomerModel customer = new CustomerModel();
            var query = from e in this.Context.Customers
                        where e.Username == name
                        select new CustomerModel()
                        {
                            FirstName = e.Firstname != null ? e.Firstname : "First Name",
                            LastName = e.Lastname != null ? e.Lastname : "Last Name",
                            Address = e.Address,
                            PhoneNumber = e.Phonenumber,
                        };
            if (query != null && query.ToList().Count > 0)
            {
                return query.First();
            }

            return null;
        }

        public string UpdateNewPassword(string name, string mail)
        {
            string newPass = SecurityUtil.ResetPass(mail);
            string newPassMD5 = SecurityUtil.MD5Encrypt(newPass);

            string sql = @"update Customer set Password = {0} where Username = {1}";
            var query = this.Context.ExecuteQuery<CustomerModel>(sql, new object[] { newPassMD5, name }).ToList();

            return newPass;
        }

        public bool SendMail(string mail, string newPass)
        {
            string body = @"<b>Password mới: </b>" + newPass;
            return MailUtil.SendMail(mail, "Reset Password", body);
        }

        public string GetAddress(int id) {
            var query = from e in this.Context.Customers
                        where e.CustomerID == id
                        select new { address = e.Address};
            if (query != null)
            {
                return ConvertUtil.ToString(query.Select(c => c.address).FirstOrDefault());
            }
            else {
                return null;
            }
        }
    }
}
